{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第6篇：合并和联接\n",
    "\n",
    "在连接/合并类型操作的情况下，pandas提供了多种功能，可以轻松地将Series或DataFrame与各种用于索引和关系代数功能的集合逻辑组合在一起。用的比较多的有四个函数：\n",
    "- `pandas.DataFrame.append`: 纵向合并两个DataFrame,在DataFrame末尾添加DataFrame(也可以添加Series)\n",
    "- `pandas.merge/pandas.DataFrame.merge`: 横向连接两个DataFrame,可以通过索引或者列\n",
    "- `pandas.DataFrame.join`: 横向联接两个DataFrame，默认以公共index进行联接，左边的DataFrame可以指定列与右边DataFrame的index进行联接，而右边不能指定列。\n",
    "- `pandas.concat`: 将多个DataFrame进行纵向合并/横向联接，axis为1时表示横向联接，且只能以公共的index进行联接，不能指定公共列。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第1部分：合并"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "导入相关库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city\n",
       "0    Tom   18   北京\n",
       "1    Bob   30   上海\n",
       "2   Mary   25   广州\n",
       "3  James   40   深圳\n",
       "4  Yafei   22   晋城"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1 = {\n",
    "    \"name\": pd.Series([\"Tom\", \"Bob\", \"Mary\", \"James\", \"Yafei\"]),\n",
    "    \"age\": pd.Series([18, 30, 25, 40, 22]),\n",
    "    \"city\": pd.Series([\"北京\", \"上海\", \"广州\", \"深圳\", \"晋城\"])\n",
    "}\n",
    "df1 = pd.DataFrame(data1)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>洛杉矶</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allen</td>\n",
       "      <td>52</td>\n",
       "      <td>费城</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Machael</td>\n",
       "      <td>43</td>\n",
       "      <td>芝加哥</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  age city\n",
       "0     Kobe   41  洛杉矶\n",
       "1    Allen   52   费城\n",
       "2  Machael   43  芝加哥"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2 = {\n",
    "    \"name\": pd.Series([\"Kobe\", \"Allen\", \"Machael\"]),\n",
    "    \"age\": pd.Series([41, 52, 43]),\n",
    "    \"city\": pd.Series([\"洛杉矶\", \"费城\", \"芝加哥\"])\n",
    "}\n",
    "df2 = pd.DataFrame(data2)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "上面有两个DataFrame：df1和df2，我们发现其均有三列且列名相同，一个是5个人的信息，一个是3个人的信息，那么如何将两个DataFrame的数据进行纵向合并呢？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### append\n",
    "> append(other, ignore_index=False,verify_integrity=False, sort=None):\n",
    "\n",
    "如果对之前的内容吸收比较好的同学可能还有印象，在介绍Series和DataFrame的时候，我们曾经介绍过append这个方法，当时是作为Series/DataFrame添加新元素/新行的方法来引入的，对于DataFrame来说，之前我们介绍过如何添加一行数据，那么可以不可以添加多行数据呢？答案当然是可以，多行数据也可以看做一个DataFrame,即实现两个具有相同列的DataFrame的合并。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>洛杉矶</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allen</td>\n",
       "      <td>52</td>\n",
       "      <td>费城</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Machael</td>\n",
       "      <td>43</td>\n",
       "      <td>芝加哥</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  age city\n",
       "0      Tom   18   北京\n",
       "1      Bob   30   上海\n",
       "2     Mary   25   广州\n",
       "3    James   40   深圳\n",
       "4    Yafei   22   晋城\n",
       "0     Kobe   41  洛杉矶\n",
       "1    Allen   52   费城\n",
       "2  Machael   43  芝加哥"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.append(df2)\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到，拼接后的索引默认还是原有的索引，如果想要重新生成索引的话，设置参数 ignore_index=True 即可。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>洛杉矶</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Allen</td>\n",
       "      <td>52</td>\n",
       "      <td>费城</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Machael</td>\n",
       "      <td>43</td>\n",
       "      <td>芝加哥</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  age city\n",
       "0      Tom   18   北京\n",
       "1      Bob   30   上海\n",
       "2     Mary   25   广州\n",
       "3    James   40   深圳\n",
       "4    Yafei   22   晋城\n",
       "5     Kobe   41  洛杉矶\n",
       "6    Allen   52   费城\n",
       "7  Machael   43  芝加哥"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.append(df2, ignore_index=True)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>Bob</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>James</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "      <td>Yafei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>41</td>\n",
       "      <td>洛杉矶</td>\n",
       "      <td>Kobe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>52</td>\n",
       "      <td>费城</td>\n",
       "      <td>Allen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>43</td>\n",
       "      <td>芝加哥</td>\n",
       "      <td>Machael</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   age city     name\n",
       "0   18   北京      Tom\n",
       "1   30   上海      Bob\n",
       "2   25   广州     Mary\n",
       "3   40   深圳    James\n",
       "4   22   晋城    Yafei\n",
       "5   41  洛杉矶     Kobe\n",
       "6   52   费城    Allen\n",
       "7   43  芝加哥  Machael"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.append(df2, ignore_index=True, sort='age')\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df1.append(df2, verify_integrity=True)\n",
    "# ---------------------------------------------------------------------------\n",
    "# ValueError                                Traceback (most recent call last)\n",
    "# <ipython-input-8-e97550fb1601> in <module>\n",
    "# ----> 1 df3 = df1.append(df2, verify_integrity=True)\n",
    "#       2 df3\n",
    "\n",
    "# F:\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py in append(self, other, ignore_index, verify_integrity, sort)\n",
    "#    7741         else:\n",
    "#    7742             to_concat = [self, other]\n",
    "# -> 7743         return concat(\n",
    "#    7744             to_concat,\n",
    "#    7745             ignore_index=ignore_index,\n",
    "\n",
    "# F:\\Anaconda3\\lib\\site-packages\\pandas\\core\\reshape\\concat.py in concat(objs, axis, join, ignore_index, keys, levels, names, verify_integrity, sort, copy)\n",
    "#     272     ValueError: Indexes have overlapping values: ['a']\n",
    "#     273     \"\"\"\n",
    "# --> 274     op = _Concatenator(\n",
    "#     275         objs,\n",
    "#     276         axis=axis,\n",
    "\n",
    "# ValueError: Indexes have overlapping values: Int64Index([0, 1, 2], dtype='int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### concat\n",
    "> concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False, keys=None, levels=None, names=None, verify_integrity=False, sort=None, copy=True):\n",
    "\n",
    "concat方法可以在两个维度上拼接，默认纵向凭借（axis=0），拼接方式默认外连接\n",
    "所谓外连接，就是取拼接方向的并集，而'inner'时取拼接方向（若使用默认的纵向拼接，则为列的交集）的交集。下面通过一些例子理解concat的用法和各个参数的意义。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>洛杉矶</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allen</td>\n",
       "      <td>52</td>\n",
       "      <td>费城</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Machael</td>\n",
       "      <td>43</td>\n",
       "      <td>芝加哥</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  age city\n",
       "0      Tom   18   北京\n",
       "1      Bob   30   上海\n",
       "2     Mary   25   广州\n",
       "3    James   40   深圳\n",
       "4    Yafei   22   晋城\n",
       "0     Kobe   41  洛杉矶\n",
       "1    Allen   52   费城\n",
       "2  Machael   43  芝加哥"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(objs=[df1, df2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>洛杉矶</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Allen</td>\n",
       "      <td>52</td>\n",
       "      <td>费城</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Machael</td>\n",
       "      <td>43</td>\n",
       "      <td>芝加哥</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  age city\n",
       "0      Tom   18   北京\n",
       "1      Bob   30   上海\n",
       "2     Mary   25   广州\n",
       "3    James   40   深圳\n",
       "4    Yafei   22   晋城\n",
       "5     Kobe   41  洛杉矶\n",
       "6    Allen   52   费城\n",
       "7  Machael   43  芝加哥"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(objs=[df1, df2], ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### assign"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city\n",
       "0    Tom   18   北京\n",
       "1    Bob   30   上海\n",
       "2   Mary   25   广州\n",
       "3  James   40   深圳\n",
       "4  Yafei   22   晋城"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>看球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>跑步</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  hobby sex\n",
       "0   羽毛球   男\n",
       "1    棒球   男\n",
       "2    舞蹈   女\n",
       "3    看球   男\n",
       "4    跑步   男"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2 = {\n",
    "    \"hobby\": pd.Series(['羽毛球', '棒球', '舞蹈', '看球', '跑步']),\n",
    "    \"sex\": pd.Series(['男', '男', '女', '男', '男'])\n",
    "}\n",
    "df2 = pd.DataFrame(data2)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "横向合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>看球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "      <td>跑步</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city hobby sex\n",
       "0    Tom   18   北京   羽毛球   男\n",
       "1    Bob   30   上海    棒球   男\n",
       "2   Mary   25   广州    舞蹈   女\n",
       "3  James   40   深圳    看球   男\n",
       "4  Yafei   22   晋城    跑步   男"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.assign(hobby=df2['hobby'], sex=df2['sex'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>看球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "      <td>跑步</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city hobby sex\n",
       "0    Tom   18   北京   羽毛球   男\n",
       "1    Bob   30   上海    棒球   男\n",
       "2   Mary   25   广州    舞蹈   女\n",
       "3  James   40   深圳    看球   男\n",
       "4  Yafei   22   晋城    跑步   男"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.assign(**{col: df2[col] for col in df2.columns})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第2部分：横向联接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### merge\n",
    "> merge(right, how='inner', on=None, left_on=None, right_on=None,left_index=False, right_index=False, sort=False,suffixes=('_x', '_y'), copy=True, indicator=False,validate=None)\n",
    "\n",
    "- how: 连接方式，默认how为inner,即内连接。\n",
    "- on为公共列，若公共列名相同，以on为公共列对两个df进行连接，若不同，可以指定left_on和right_on。on可以为一个公共列，也可以为多个公共列，多个用列表表示。\n",
    "- left_index, right_index: 是否以索引对齐进行连接，默认为False。\n",
    "- sort: 是否排序\n",
    "- suffixes: 重复的列后缀命名方式，默认为['_x', '_y']\n",
    "- indicatos: 是否显示改行索引的来源，默认不显示\n",
    "- validate: 如果为True,检查两边的索引属于哪种类型，有one_to_one（两边都唯一）/one_to_many（左唯一，右重复）/many_to_one（左重复，右唯一）/many_to_many(左右都重复)三种。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city\n",
       "0    Tom   18   北京\n",
       "1    Bob   30   上海\n",
       "2   Mary   25   广州\n",
       "3  James   40   深圳\n",
       "4  Yafei   22   晋城"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>看球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Allen</td>\n",
       "      <td>41</td>\n",
       "      <td>跑步</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age hobby sex\n",
       "0    Bob   30   羽毛球   男\n",
       "1   Mary   25    棒球   男\n",
       "2  James   40    舞蹈   女\n",
       "3   Kobe   41    看球   男\n",
       "4  Allen   41    跑步   男"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2 = {\n",
    "    \"name\": pd.Series([\"Bob\", \"Mary\", \"James\", \"Kobe\", \"Allen\"]),\n",
    "    \"age\": pd.Series([30, 25, 40, 41, 41]),\n",
    "    \"hobby\": pd.Series(['羽毛球', '棒球', '舞蹈', '看球', '跑步']),\n",
    "    \"sex\": pd.Series(['男', '男', '女', '男', '男'])\n",
    "}\n",
    "df2 = pd.DataFrame(data2)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "on：公共列，suffixes: 重复列后缀"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age_x</th>\n",
       "      <th>city</th>\n",
       "      <th>age_y</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>30</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>25</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>40</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age_x city  age_y hobby sex\n",
       "0    Bob     30   上海     30   羽毛球   男\n",
       "1   Mary     25   广州     25    棒球   男\n",
       "2  James     40   深圳     40    舞蹈   女"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.merge(df2, on='name', how='inner')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age-df1</th>\n",
       "      <th>city</th>\n",
       "      <th>age-df2</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>30</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>25</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>40</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age-df1 city  age-df2 hobby sex\n",
       "0    Bob       30   上海       30   羽毛球   男\n",
       "1   Mary       25   广州       25    棒球   男\n",
       "2  James       40   深圳       40    舞蹈   女"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.merge(df2, on='name', how='inner', suffixes=['-df1', '-df2'])\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "validate检验的是到底哪一边出现了重复索引，如果是“one_to_one”则两侧索引都是唯一，如果\"one_to_many\"则左侧唯一"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name_x</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>name_y</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>Bob</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>Mary</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>James</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  name_x  age city name_y hobby sex\n",
       "0    Bob   30   上海    Bob   羽毛球   男\n",
       "1   Mary   25   广州   Mary    棒球   男\n",
       "2  James   40   深圳  James    舞蹈   女"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.merge(df2, on='age', how='inner')\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df3 = df1.merge(df2, on='age', how='right', validate='one_to_one')\n",
    "# ---------------------------------------------------------------------------\n",
    "# MergeError                                Traceback (most recent call last)\n",
    "# <ipython-input-41-0735a25e52d7> in <module>\n",
    "# ----> 1 df3 = df1.merge(df2, on='age', how='inner', validate='one_to_one')\n",
    "#       2 df3\n",
    "# ...\n",
    "# MergeError: Merge keys are not unique in right dataset; not a one-to-one merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name_x</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>name_y</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>Bob</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>Mary</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>James</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kobe</td>\n",
       "      <td>看球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Allen</td>\n",
       "      <td>跑步</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  name_x  age city name_y hobby sex\n",
       "0    Bob   30   上海    Bob   羽毛球   男\n",
       "1   Mary   25   广州   Mary    棒球   男\n",
       "2  James   40   深圳  James    舞蹈   女\n",
       "3    NaN   41  NaN   Kobe    看球   男\n",
       "4    NaN   41  NaN  Allen    跑步   男"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.merge(df2, on='age', how='right', validate='one_to_many')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "on也可以是多个公共列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city hobby sex\n",
       "0    Bob   30   上海   羽毛球   男\n",
       "1   Mary   25   广州    棒球   男\n",
       "2  James   40   深圳    舞蹈   女"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.merge(df2, on=['name', 'age'], how='inner')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "有时候，两个 DataFrame 中需要关联的键的名称不一样，可以通过 left_on 和 right_on 来分别设置。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name1</th>\n",
       "      <th>age1</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name1  age1 city\n",
       "0    Tom    18   北京\n",
       "1    Bob    30   上海\n",
       "2   Mary    25   广州\n",
       "3  James    40   深圳\n",
       "4  Yafei    22   晋城"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df11 = df1.rename(columns={\"name\": \"name1\", \"age\": 'age1'})\n",
    "df21 = df2.rename(columns={\"name\": \"name2\", 'age': 'age2'})\n",
    "df11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name2</th>\n",
       "      <th>age2</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>看球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Allen</td>\n",
       "      <td>41</td>\n",
       "      <td>跑步</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name2  age2 hobby sex\n",
       "0    Bob    30   羽毛球   男\n",
       "1   Mary    25    棒球   男\n",
       "2  James    40    舞蹈   女\n",
       "3   Kobe    41    看球   男\n",
       "4  Allen    41    跑步   男"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name1</th>\n",
       "      <th>age1</th>\n",
       "      <th>city</th>\n",
       "      <th>name2</th>\n",
       "      <th>age2</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name1  age1 city  name2  age2 hobby sex\n",
       "0    Bob    30   上海    Bob    30   羽毛球   男\n",
       "1   Mary    25   广州   Mary    25    棒球   男\n",
       "2  James    40   深圳  James    40    舞蹈   女"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df11.merge(df21, left_on=['name1', 'age1'], right_on=['name2', 'age2'])\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "关联后发现数据变少了，只有 3 行数据，这是因为默认关联的方式是 inner，如果不想丢失任何数据，可以设置参数 howw\"outer\",如果我们想保留左边所有的数据，可以设置参数 how=\"left\"；反之，如果想保留右边的所有数据，可以设置参数 how=\"right\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "左连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city hobby  sex\n",
       "0    Tom   18   北京   NaN  NaN\n",
       "1    Bob   30   上海   羽毛球    男\n",
       "2   Mary   25   广州    棒球    男\n",
       "3  James   40   深圳    舞蹈    女\n",
       "4  Yafei   22   晋城   NaN  NaN"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.merge(df2, on=['name', 'age'], how='left')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "右连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>看球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Allen</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>跑步</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city hobby sex\n",
       "0    Bob   30   上海   羽毛球   男\n",
       "1   Mary   25   广州    棒球   男\n",
       "2  James   40   深圳    舞蹈   女\n",
       "3   Kobe   41  NaN    看球   男\n",
       "4  Allen   41  NaN    跑步   男"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.merge(df2, on=['name', 'age'], how='right')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "外连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>看球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Allen</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>跑步</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city hobby  sex\n",
       "0    Tom   18   北京   NaN  NaN\n",
       "1    Bob   30   上海   羽毛球    男\n",
       "2   Mary   25   广州    棒球    男\n",
       "3  James   40   深圳    舞蹈    女\n",
       "4  Yafei   22   晋城   NaN  NaN\n",
       "5   Kobe   41  NaN    看球    男\n",
       "6  Allen   41  NaN    跑步    男"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.merge(df2, on=['name', 'age'], how='outer')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "indicator参数指示了，合并后该行索引的来源"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Kobe</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>看球</td>\n",
       "      <td>男</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Allen</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>跑步</td>\n",
       "      <td>男</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city hobby  sex      _merge\n",
       "0    Tom   18   北京   NaN  NaN   left_only\n",
       "1    Bob   30   上海   羽毛球    男        both\n",
       "2   Mary   25   广州    棒球    男        both\n",
       "3  James   40   深圳    舞蹈    女        both\n",
       "4  Yafei   22   晋城   NaN  NaN   left_only\n",
       "5   Kobe   41  NaN    看球    男  right_only\n",
       "6  Allen   41  NaN    跑步    男  right_only"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.merge(df2, on=['name', 'age'], how='outer', indicator=True)\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### join\n",
    "> join(other, on=None, how='left', lsuffix='', rsuffix='',sort=False)\n",
    "\n",
    "除了 merge 这种方式外，还可以通过 join 这种方式实现关联。join函数作用是将多个pandas对象横向拼接，遇到重复的索引项时会使用笛卡尔积，默认左连接，可选inner、outer、right连接相比 merge，join 这种方式有以下几个不同：\n",
    "- 默认参数on=None，表示关联时使用左边和右边的索引作为键，设置参数on可以指定的是关联时左边的所用到的键名\n",
    "- 左边和右边字段名称重复时，通过设置参数 lsuffix 和 rsuffix 来解决。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age-df1</th>\n",
       "      <th>city</th>\n",
       "      <th>age-df2</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>30.0</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>25.0</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>40.0</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age-df1 city  age-df2 hobby  sex\n",
       "0    Tom       18   北京      NaN   NaN  NaN\n",
       "1    Bob       30   上海     30.0   羽毛球    男\n",
       "2   Mary       25   广州     25.0    棒球    男\n",
       "3  James       40   深圳     40.0    舞蹈    女\n",
       "4  Yafei       22   晋城      NaN   NaN  NaN"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.join(df2.set_index('name'), on='name', lsuffix='-df1', rsuffix='-df2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>18</td>\n",
       "      <td>北京</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James</td>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yafei</td>\n",
       "      <td>22</td>\n",
       "      <td>晋城</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age city hobby  sex\n",
       "0    Tom   18   北京   NaN  NaN\n",
       "1    Bob   30   上海   羽毛球    男\n",
       "2   Mary   25   广州    棒球    男\n",
       "3  James   40   深圳    舞蹈    女\n",
       "4  Yafei   22   晋城   NaN  NaN"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.join(df2.set_index(['name', 'age']), on=['name', 'age'], lsuffix='-df1', rsuffix='-df2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "注：连接的df2必须指定索引进行连接，df1可以是索引，也可以是指定列，所为指定列时必须指定on"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### concat\n",
    "\n",
    "> concat(objs, axis=1, join='outer', join_axes=None, ignore_index=False, keys=None, levels=None, names=None, verify_integrity=False, sort=None, copy=True):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>age</th>\n",
       "      <th>hobby</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>30</td>\n",
       "      <td>上海</td>\n",
       "      <td>30</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mary</th>\n",
       "      <td>25</td>\n",
       "      <td>广州</td>\n",
       "      <td>25</td>\n",
       "      <td>棒球</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>James</th>\n",
       "      <td>40</td>\n",
       "      <td>深圳</td>\n",
       "      <td>40</td>\n",
       "      <td>舞蹈</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       age city  age hobby sex\n",
       "name                          \n",
       "Bob     30   上海   30   羽毛球   男\n",
       "Mary    25   广州   25    棒球   男\n",
       "James   40   深圳   40    舞蹈   女"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(objs=[df1.set_index('name'), df2.set_index('name')], join='inner', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "注：concat方法对两个df进行横向连接时，axis必须设置为1,只能以两个df共同的index进行连接，即不能指定公共列，连接方式可以通过指定join参数，默认为outer，即外连接。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第3部分：融汇贯通\n",
    "\n",
    "将上述学到的合并联接方法整理成通用函数，下面是我整理的，可以参考。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "from pandas import read_csv, read_excel, merge, concat, DataFrame\n",
    "\n",
    "\n",
    "def read_file(file_path, on):\n",
    "    if file_path.endswith('.csv'):\n",
    "        return read_csv(file_path)\n",
    "    if file_path.endswith('.xls') or file_path.endswith('xlsx'):\n",
    "        return read_excel(file_path)\n",
    "\n",
    "\n",
    "def df_to_file(df: DataFrame, file_path: str, index: bool = True, encoding: str = 'utf_8_sig'):\n",
    "    if file_path.endswith('.csv'):\n",
    "        df.to_csv(file_path, index=index, encoding=encoding)\n",
    "    if file_path.endswith('.xls') or file_path.endswith('xlsx'):\n",
    "        df.to_excel(file_path, index=index)\n",
    "\n",
    "\n",
    "def merge_two_data(file1: str, file2: str, on: str = None, left_on: str = None, right_on: str = None,\n",
    "                   how: str = 'inner', to_file: str = None):\n",
    "    \"\"\"\n",
    "    横向合并两个文件\n",
    "    @param file1:\n",
    "    @param file2:\n",
    "    @param on:\n",
    "    @param left_on:\n",
    "    @param right_on:\n",
    "    @param how:\n",
    "    @param to_file:\n",
    "    @return:\n",
    "    \"\"\"\n",
    "    df1 = read_file(file1)\n",
    "    df2 = read_file(file2)\n",
    "    merge_df = merge(df1, df2, on=on, how=how, left_on=left_on, right_on=right_on)\n",
    "    if to_file:\n",
    "        if to_file.endswith('.csv'):\n",
    "            merge_df.to_csv(to_file, encoding='utf_8_sig', index=False)\n",
    "        elif to_file.endswith('xls') or to_file.endswith('xlsx'):\n",
    "            merge_df.to_excel(to_file, index=False)\n",
    "    else:\n",
    "        return merge_df\n",
    "\n",
    "\n",
    "def append_two_file(file1: str, file2: str, ignore_index: bool = True, to_file: str = None):\n",
    "    \"\"\"\n",
    "    纵向合并两个文件\n",
    "    @param file1:\n",
    "    @param file2:\n",
    "    @param to_file:\n",
    "    @return:\n",
    "    \"\"\"\n",
    "    df1 = read_file(file1)\n",
    "    df2 = read_file(file2)\n",
    "    df3 = df1.append(df2, ignore_index=ignore_index)\n",
    "    if to_file:\n",
    "        df_to_file(df3, to_file, index=False)\n",
    "    else:\n",
    "        return df3\n",
    "\n",
    "\n",
    "def join_two_file(file1: str, file2: str, on=None, left_on=None, right_on=None, how: str = 'left', to_file: str = None):\n",
    "    \"\"\"\n",
    "    横向联接两个表格文件\n",
    "    @param file1:\n",
    "    @param file2:\n",
    "    @param on:\n",
    "    @param left_on:\n",
    "    @param right_on:\n",
    "    @param how:\n",
    "    @param to_file:\n",
    "    @return: None or DataFrame\n",
    "    \"\"\"\n",
    "    if on:\n",
    "        df1 = read_file(file1).set_index(keys=on)\n",
    "        df2 = read_file(file2).set_index(keys=on)\n",
    "    elif left_on or right_on:\n",
    "        df1 = read_file(file1).set_index(keys=on) if left_on else read_file(file1)\n",
    "        df2 = read_file(file2).set_index(keys=on) if right_on else read_file(file2)\n",
    "    else:\n",
    "        df1 = read_file(file1)\n",
    "        df2 = read_file(file2)\n",
    "    df3 = df1.join(df2, how=how)\n",
    "    if to_file:\n",
    "        df_to_file(df3, to_file, index=False)\n",
    "    else:\n",
    "        return df3\n",
    "\n",
    "\n",
    "def concat_mul_file(axis: int = 0, on=None, to_file: str = None, encoding: str = 'utf_8_sig', *files):\n",
    "    \"\"\"\n",
    "    多个表格文件合并\n",
    "    @param axis: 0/index 1/column 若axis=1, 默认基于索引将多个文件合并\n",
    "    @param on: 当axis=1时，指定索引列/索引列\n",
    "    @param to_file: 导出文件路径\n",
    "    @param encoding: 导出文件编码\n",
    "    @param files: 合并文件路径\n",
    "    @return:\n",
    "    \"\"\"\n",
    "    if len(files) > 1:\n",
    "        if axis == 1 and on:\n",
    "            objs = [read_file(file).set_index(keys=on) for file in files]\n",
    "        else:\n",
    "            objs = [read_file(file) for file in files]\n",
    "        merge_data = concat(objs=objs, axis=axis)\n",
    "        if to_file:\n",
    "            df_to_file(merge_data, to_file, index=False, encoding=encoding)\n",
    "        else:\n",
    "            return merge_data\n",
    "    else:\n",
    "        raise Exception('合并的文件个数小于2，不能进行合并，请输入大于等于两个文件路径')\n",
    " ```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 简单小结\n",
    "数据合并和联接是数据处理中常用的操作技巧，pandas为相关操作提供了非常方便的方法，通过本节的内容，可以学会利用这些方法并在实际工作中高效的提高你的效率，这也是我们学习编程的意义之一，就是学以致用。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
